Modern database systems are very complex, comprised of millions of lines of code and numerous components, each component performing operations in memory. Database systems often are used for performance-sensitive operations. As a result, a database system administrator may desire to use statistics in order to measure performance of the database system, or to isolate problems.
To effectively diagnose a performance problem, it is vital to have an established performance baseline for later comparison when the system is running poorly. Without a baseline data point, it can be very difficult to identify new problems. For example, perhaps the volume of transactions on the system has increased, or the transaction profile or application has changed, or the number of users has increased.
A common approach to using statistics to diagnose problems or optimize performance is to gather statistics that cover the time when the instance had the performance problem. If you previously captured baseline data for comparison, then you can compare the current data to the data from the baseline that most represents the problem workload.
An example of a statistic a database system administrator may inquire about is session logical reads. A session logical read results when a user goes to read a buffer from the buffer cache, for example, to read one buffer holding data from the “Employee” table. A database system administrator may desire to see all session logical reads for a particular user session, or over a period of time. Time may be measured in terms of clock time or CPU time. CPU time is often computed by reading the CPU time when an operation begins, and reading it again upon completion of the operation. The difference between the two is the CPU time taken by the operation.
Another approach to using statistics for diagnostic or tuning purposes is to take a snapshot of values. A snapshot can be a collection of performance statistics that are captured at a specific point in time. The snapshots are used to compute the delta in the statistic value, the rate of change for the statistic, or if appropriate, the value which was current at the time the snapshot was taken. These snapshots may be gathered at specific intervals; for example, every 30 seconds or every five minutes. Storing the snapshots allows a user to evaluate statistics from past points in time. For example, a snapshot of a database system at one point in time may indicate 50 session logical reads have occurred, while a snapshot at a later time may indicate 150 session logical reads have occurred. From these snapshots, the user can determine that the number of session logical reads that have occurred between the time of the two snapshots is 100.
The snapshot data points may be used to compute the rate of change for the statistic being measured. Storing statistics generated by the database system allows administrators to analyze problems that occurred in the past. The stored statistics also allow the database system's current performance to be compared to a stored baseline.
A current approach to evaluating this type of statistical data is to manually compare reports generated for the two periods. This approach has several drawbacks. One, given the large number of performance statistics potentially tracked by the system, it is difficult to accurately isolate key statistical differences. Two, the periods may not have the same length, such as comparing one hour to two hours, or may not have the same density, such as comparing a period where 100 user requests took place versus a period where 200 user requests took place.
Therefore, to evaluate whether an upgrade affected performance, a current approach is to take a set of snapshots of statistics at a point prior to the upgrade and a set of snapshots after the upgrade. A report is generated detailing the differences, which could be dozens of pages long depending on the number of statistics gathered. The user must manually read the report and try and figure out what is different and quantify it.
Consequently, there is a need for an improved technique that does not suffer from the shortcomings previously described.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, the approaches described in this section may not be prior art to the claims in this application and are not admitted to be prior art by inclusion in this section.